﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;


public class DataProcess
{
    SqlConnection conn;
    SqlCommand cmd;
	public DataProcess()
	{
        conn = new SqlConnection("server=localhost ; database = OnlineBooking;uid = sa ; pwd=12345678");
	}
    public bool loginAdmin(string user, string pass) {

        if (conn.State == ConnectionState.Closed)
            conn.Open();
        string sql = "select * from AccountAdmin where UserName=@user and PassWord=@pass";
        cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@user", user);
        cmd.Parameters.AddWithValue("@pass", pass);
        SqlDataReader data = cmd.ExecuteReader();
        bool result = data.HasRows;
        conn.Close();
        return result;

    
    }
    public DataTable getAllNew() {

        if (conn.State == ConnectionState.Closed)
            conn.Open();
        string sql = "select * from New";
        cmd = new SqlCommand(sql, conn);
        SqlDataAdapter data = new SqlDataAdapter(cmd);
        DataTable tb = new DataTable();
        data.Fill(tb);
        conn.Close();
        return tb;
    
    }
    public DataTable getAllHotline()
    {

        if (conn.State == ConnectionState.Closed)
            conn.Open();
        string sql = "select * from Hotline ";
        cmd = new SqlCommand(sql, conn);
        SqlDataAdapter data = new SqlDataAdapter(cmd);
        DataTable tb = new DataTable();
        data.Fill(tb);
        conn.Close();
        return tb;

    }

    public DataTable getAllAdvById(int id)
    {

        if (conn.State == ConnectionState.Closed)
            conn.Open();
        string sql = "select * from Adv where ADV_ID=@id ";
        cmd = new SqlCommand(sql, conn);
        SqlDataAdapter data = new SqlDataAdapter(cmd);
        cmd.Parameters.AddWithValue("@id", id);
        DataTable tb = new DataTable();
        data.Fill(tb);
        conn.Close();
        return tb;

    }

    public DataTable getAllAdv()
    {

        if (conn.State == ConnectionState.Closed)
            conn.Open();
        string sql = "select * from Adv ";
        cmd = new SqlCommand(sql, conn);
        SqlDataAdapter data = new SqlDataAdapter(cmd);
        DataTable tb = new DataTable();
        data.Fill(tb);
        conn.Close();
        return tb;

    }
    public DataTable searchAdv(string txtadv)
    {

        if (conn.State == ConnectionState.Closed)
            conn.Open();
        string sql = "select * from Adv where ADV_Position=@txtadv ";
        cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@txtadv", txtadv);
        SqlDataAdapter data = new SqlDataAdapter(cmd);
        DataTable tb = new DataTable();
        data.Fill(tb);
        conn.Close();
        return tb;

    }
    public DataTable getAllAdvright()
    {

        if (conn.State == ConnectionState.Closed)
            conn.Open();
        string sql = "select * from Adv where ADV_Position='advright' and ADV_Status='Yes' ";
        cmd = new SqlCommand(sql, conn);
        SqlDataAdapter data = new SqlDataAdapter(cmd);
        DataTable tb = new DataTable();
        data.Fill(tb);
        conn.Close();
        return tb;

    }
    public DataTable getAllAdvleft()
    {

        if (conn.State == ConnectionState.Closed)
            conn.Open();
        string sql = "select * from Adv where ADV_Position='advleft' and ADV_Status='Yes' ";
        cmd = new SqlCommand(sql, conn);
        SqlDataAdapter data = new SqlDataAdapter(cmd);
        DataTable tb = new DataTable();
        data.Fill(tb);
        conn.Close();
        return tb;

    }
    public DataTable getAllAdvBottom()
    {

        if (conn.State == ConnectionState.Closed)
            conn.Open();
        string sql = "select * from Adv where ADV_Position='advbottom' and ADV_Status='Yes' ";
        cmd = new SqlCommand(sql, conn);
        SqlDataAdapter data = new SqlDataAdapter(cmd);
        DataTable tb = new DataTable();
        data.Fill(tb);
        conn.Close();
        return tb;

    }
    public bool addAdv(string name , string position , string link , string target , string file,string status)
    {
        if (conn.State == ConnectionState.Closed)
            conn.Open();
        string sql = "insert into Adv values(@name,@position,@file,@link,@target,@status)";
        cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@name", name);
        cmd.Parameters.AddWithValue("@position", position);
        cmd.Parameters.AddWithValue("@link", link);
        cmd.Parameters.AddWithValue("@target", target);
        cmd.Parameters.AddWithValue("@file", file);
        cmd.Parameters.AddWithValue("@status", status);
        int n = cmd.ExecuteNonQuery();
        bool result = (n > 0);
        conn.Close();
        return result;
    }
    public bool editAdv(string name, string position, string link, string target, string file, string status , int id)
    {
        if (conn.State == ConnectionState.Closed)
            conn.Open();
        string sql = "update Adv set ADV_Name=@name , ADV_Position=@position , ADV_Image=@file , ADV_Link=@link , ADV_Target=@target,ADV_Status=@status where ADV_ID=@id";
        cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@name", name);
        cmd.Parameters.AddWithValue("@position", position);
        cmd.Parameters.AddWithValue("@link", link);
        cmd.Parameters.AddWithValue("@target", target);
        cmd.Parameters.AddWithValue("@file", file);
        cmd.Parameters.AddWithValue("@status", status);
        cmd.Parameters.AddWithValue("@id", id);
        int n = cmd.ExecuteNonQuery();
        bool result = (n > 0);
        conn.Close();
        return result;
    }
    public bool delAdv(int id)
    {
        if (conn.State == ConnectionState.Closed)
            conn.Open();
        string sql = "delete from Adv where ADV_ID=@id";
        cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@id", id);
        int n = cmd.ExecuteNonQuery();
        bool result = (n > 0);
        conn.Close();
        return result;
    }
}